#include <sqlite3.h>
#include <my_head.h>

#include "mysqlite.h"

sqlite3 *db = NULL;

#define N 800

//判断是否存在
int do_itexists(char *sqlbuff, msg_t *msg);

//表名：staff
//初始化
int do_init_sqlite3(){

    if (SQLITE_OK != sqlite3_open(DATABASE, &db))
    {   
        printf("sqlite3_open error\n");
        return -1;
    }
    char sqlbuff[N] = "CREATE TABLE IF NOT EXISTS staff(name INT PRIMARY KEY,\
                                                            age INT,\
                                                            phone CHAR,\
                                                            sex CHAR,\
                                                            position CHAR,\
                                                            salary INT,\
                                                            address CHAR,\
                                                            password CHAR,\
                                                            root INT)";
                                                            
    if (SQLITE_OK != sqlite3_exec(db, sqlbuff, NULL, NULL, NULL))
    {   
        printf("创建表失败\n");
        return -1;
    }
    msg_t msg;
    strcpy(msg.name, "zhangsan");
    msg.age = 18;
    strcpy(msg.phone, "13521622479");
    strcpy(msg.sex, "nan");
    strcpy(msg.position, "admin");
    msg.salary = 1200;
    strcpy(msg.address, "beijingchaoyang");
    strcpy(msg.password, "111111");
    msg.root = 1;
    

    do_insert(&msg);
    printf("打开表成功\n");
    return 0;
}

//登录
void do_login(msg_t *msg){

    char sqlbuff[N] = {0};
    sprintf(sqlbuff, "SELECT * FROM staff WHERE name='%s' AND password='%s'", msg->name, msg->password);
    //printf("sql:%s\n",sqlbuff);
    char **result = NULL;
    int nrow = 0, ncolun = 0;
    if (SQLITE_OK != sqlite3_get_table(db, sqlbuff, &result, &nrow, &ncolun, NULL))
    {   
        printf("login sqlite3_get_table error");
        strcpy(msg->tip, "登录失败!");
    }
    if (nrow == 0)
    {   
        strcpy(msg->tip, "用户名或密码错误！");
    }else{
        memset(sqlbuff, 0, sizeof(sqlbuff));
        sprintf(sqlbuff, "SELECT * FROM staff WHERE name='%s' AND root= 1", msg->name);
        sqlite3_get_table(db, sqlbuff, &result, &nrow, &ncolun, NULL);
        msg->root = 0;
        if (nrow == 1)
        {
            msg->root = 1;
        }
        strcpy(msg->tip, "登录成功");
    }
    sqlite3_free_table(result);
}
//增加
void do_insert(msg_t *msg){
    char sqlbuff[N] = {0};                                        
    sprintf(sqlbuff, "INSERT INTO staff VALUES('%s', %d, '%s', '%s', '%s', %d, '%s', '%s', %d)",
    msg->name,msg->age,msg->phone,msg->sex,msg->position,msg->salary,msg->address,msg->password,msg->root);
     
    int ret = 0;
    if (SQLITE_OK != (ret = sqlite3_exec(db, sqlbuff, NULL, NULL, NULL)))
    {
        if (ret == 19)
        {
            strcpy(msg->tip, "添加失败，此用户已存在!");
        }else{
            strcpy(msg->tip, "添加失败，请联系管理员");
        }
        return;
    }
    strcpy(msg->tip, "添加成功");
}
//删除
void do_delete(msg_t *msg){

    char sqlbuff[N] = {0};
    sprintf(sqlbuff, "SELECT * FROM staff WHERE name='%s'", msg->name);
    if (do_itexists(sqlbuff, msg) != 0)
    {
        return;
    }
    
    memset(sqlbuff, 0, sizeof(sqlbuff));
    sprintf(sqlbuff, "SELECT * FROM staff WHERE name='%s' AND root=1", msg->name);
    char **result = NULL;
    int nrow = 0, ncolumn = 0;
    if (SQLITE_OK != sqlite3_get_table(db, sqlbuff, &result, &nrow, &ncolumn, NULL))
    {   
        strcpy(msg->tip, "删除失败");
        return;
    }
    if (nrow == 1)
    {   
        strcpy(msg->tip, "此用户是root用户, 不能删除");
        return;
    }
    sqlite3_free_table(result);

    memset(sqlbuff, 0, sizeof(sqlbuff));
    sprintf(sqlbuff, "DELETE FROM staff WHERE name='%s'", msg->name);
    if (SQLITE_OK != sqlite3_exec(db, sqlbuff, NULL, NULL, NULL))
    {
        strcpy(msg->tip, "删除用户失败");
    }else{
        strcpy(msg->tip, "删除成功");
    }
}
//修改
void do_modify(msg_t *msg){

    char sqlbuff[N] = {0};
    sprintf(sqlbuff, "SELECT * FROM staff WHERE name='%s'", msg->name);
    if (do_itexists(sqlbuff, msg) != 0)
    {
        return;
    }
    
    memset(sqlbuff, 0, sizeof(sqlbuff));
    sprintf(sqlbuff, "UPDATE staff SET age=%d,phone='%s',position='%s',salary=%d,address='%s',password='%s' WHERE name='%s'",
                                    msg->age,msg->phone,msg->position,msg->salary,msg->address,msg->password,msg->name);
    if (SQLITE_OK != sqlite3_exec(db, sqlbuff, NULL, NULL, NULL))
    {
        strcpy(msg->tip, "修改失败");
    }else{
        strcpy(msg->tip, "修改成功");
    }
}
//查询回调
int query_num = 0;
int callback(void *arg, int f_num, char **f_value, char **f_name){

    int i = 0;
    int acceptfd = *(int *)arg;
    msg_t msg;
    strcpy(msg.name, f_value[0]);
    msg.age = atoi(f_value[1]);
    strcpy(msg.phone, f_value[2]);
    strcpy(msg.sex, f_value[3]);
    strcpy(msg.position, f_value[4]);
    msg.salary = atoi(f_value[5]);
    strcpy(msg.address, f_value[6]);
    strcpy(msg.password, f_value[7]);
    msg.root = atoi(f_value[8]);
    msg.search_count = query_num;
    printf("个数--%d---%d\n",msg.search_count, query_num);
    send(acceptfd, &msg, sizeof(msg_t), 0);
    return 0;
}
//查询
int do_query(int acceptfd, msg_t *msg){

    query_num = 0;
    char sqlbuff[N] = {0};
    if (msg->search_type == 0)
    {
        sprintf(sqlbuff, "SELECT * FROM staff WHERE name='%s'", msg->name);
    }else if(msg->search_type == 1){
        strcpy(sqlbuff, "SELECT * FROM staff");
    }else if (msg->search_type == 2)//薪资降序
    {
        strcpy(sqlbuff, "SELECT * FROM staff ORDER BY salary DESC");
    }
    
    if (do_itexists(sqlbuff, msg) != 0)
    {
        return -1;
    }
    
    if (SQLITE_OK != sqlite3_exec(db, sqlbuff, callback, (void *)&acceptfd, NULL))
    {
        strcpy(msg->tip, "查询失败");
        return -1;
    }
    return 0;
}

//数据库是否存在用户
int do_itexists(char *sqlbuff, msg_t *msg)
{
    char **result = NULL;
    int nrow = 0, ncolumn = 0;
    if (SQLITE_OK != sqlite3_get_table(db, sqlbuff, &result, &nrow, &ncolumn, NULL))
    {   
        printf("查询失败\n");
        return -1;
    }
    //printf("sql:%s  个数：%d\n", sqlbuff, nrow);
    if (nrow == 0)
    {   
        strcpy(msg->tip, "此用户不存在");
        return 1;
    }
    query_num = nrow;
    printf("查询到的个数：%d\n", query_num);
    sqlite3_free_table(result);
    return 0;
}

//关闭数据苦
void do_closeSql(){

    sqlite3_close(db);
}
